# ================== 导入包 =======================
import pandas as pd
import re
from db_setting.base import declarative_base,sessionmaker,engine
from db_setting.models import DynamicCostSummary,CostSubjectSummary
from solve_file.vector_db_utils import vn
import numpy as np
import uuid
from decimal import Decimal, InvalidOperation
from sqlalchemy.schema import CreateTable
from sqlalchemy.dialects import mysql 


SessionLocal = sessionmaker(bind=engine)
db = SessionLocal()


# ================== 处理数据 - 动态成本 =======================
def parse_row(row, indent_width=2):
    """
    根据编码识别层级结构，提取特殊符号及其内容，只保留类别为“科目”的行。

    Args:
        row: pandas DataFrame 的一行数据
        indent_width: 每多少个空格算作一个缩进层级，默认每两个空格算一级（未使用）

    Returns:
        tuple(level, code, name, symbol, symbol_content) 或 None（如果不是“科目”）
    """
    # 获取字段
    if isinstance(row, dict):
        subject_name = str(row.get('科目名称', '')).rstrip()
        category = str(row.get('类别', ''))
    else:
        subject_name = str(row['科目名称']).rstrip()
        category = str(row['类别'])

    # 只保留“科目”类别的行
    if category != '科目':
        return None

    # 提取编码和名称
    # 匹配括号中的编码，如 (A), (A.01), (A.01.01), (A.01.05.01)
    code_match = re.search(r'\((.*?)\)$', subject_name)
    if code_match:
        code = code_match.group(1).strip()  # 提取括号中的编码
        # 去除编码部分
        name = re.sub(r'\s*\(.*?\)$', '', subject_name).strip()
    else:
        code = ""
        name = subject_name.strip()

    # 去除特殊符号，获取纯文本名称
    name = re.sub(r'^\[[\+\-]\]\s*', '', name).strip()

    # 根据编码中的点号计算层级
    if code:
        level = code.count('.')  # 点号数量决定层级
    else:
        level = ''  # 无编码时，默认为 level 0

    return level, code, name


# --- 工具函数：金额清洗，转 float ---
def clean_amount(amount_str):
    if pd.isna(amount_str):
        return 0.0
    try:
        clean_str = str(amount_str).replace(',', '').strip()
        return float(clean_str)
    except Exception:
        return 0.0

def clean_amount_fields(df, columns):
    for col in columns:
        if col in df.columns:
            df[col] = df[col].apply(clean_amount)
        else:
            print(f"字段未找到: {col}")
    return df


def safe_decimal(val):
    if val is None or val == "":
        return Decimal("0.00")
    try:
        val_str = str(val).replace(",", "").strip()
        return Decimal(val_str)
    except InvalidOperation:
        return Decimal("0.00")

    
# --- 整体处理逻辑 ---
def solve_data_dynamic(df):
    print("==========开始数据清洗============")

    # 解析行并过滤非“科目”
    parsed = df.apply(parse_row, axis=1)
    df_parsed = parsed[parsed.notna()]
    df = df.loc[df_parsed.index].copy()

    # 拆分结果
    df[['level', 'code', 'name']] = pd.DataFrame(df_parsed.tolist(), index=df_parsed.index)

    # 初始化层级列
    hierarchy_cols = ['level_0', 'level_1', 'level_2', 'level_3']
    for col in hierarchy_cols:
        df[col] = ''
    df['full_path'] = ''

    # 跟踪每个层级最近的科目名称
    last_seen = {0: '', 1: '', 2: '', 3: ''}

    # 按表格顺序处理每行
    for idx, row in df.iterrows():
        level = row['level']
        name = row['name']

        # 更新当前层级的名称
        last_seen[level] = name

        subject_name = row['name']

        # 1. 填充当前层级（level_n）
        if 0 <= level < len(hierarchy_cols):
            df.at[idx, hierarchy_cols[level]] = subject_name


        # 填充 full_path
        hierarchy = [last_seen.get(i, '') for i in range(level + 1) if last_seen.get(i, '')]
        df.at[idx, 'full_path'] = ' > '.join(hierarchy)


    amount_fields = [
        '本次目标+调整成本', '合同有效签约金额', '上次签约金额', '本次签约金额',
        '上次变更金额', '本次变更金额', '上次在途成本', '本次在途成本',
        '上次合同+变更(不含在途)', '本次合同+变更(不含在途)',
        '上次预估变更', '本次预估变更',
        '上次待发生合约规划', '本次待发生合约规划',
        '上次动态成本', '本次动态成本'
    ]
    df = clean_amount_fields(df, amount_fields)

    # 合并所有字段
    for col in hierarchy_cols + ['code', 'full_path']:
        if col not in df.columns:
            df[col] = None

    print("==========数据清洗完成============")

    print("==========开始将数据保存至数据库============")
    df = df.replace({np.nan: None})
    records = []
    ddl = str(CreateTable(DynamicCostSummary.__table__).compile(dialect=mysql.dialect()))
    print("==========开始将数据保存至向量数据库============")
    vn.add_ddl(ddl)
    vn.add_ddl_details(ddl)

    for i, row in df.iterrows():
        try:
            record = DynamicCostSummary(
                id=str(uuid.uuid4()),
                subject_name = row.get("name", ""),
                category = row.get("类别", ""),
                target_adjusted_cost = safe_decimal(row.get("本次目标+调整成本")),
                contract_status = row.get("合同状态", ""),

                contract_signed_current = safe_decimal(row.get("合同有效签约金额")),
                signed_last = safe_decimal(row.get("上次签约金额")),
                signed_current = safe_decimal(row.get("本次签约金额")),

                change_last = safe_decimal(row.get("上次变更金额")),
                change_current = safe_decimal(row.get("本次变更金额")),

                pipeline_cost_last = safe_decimal(row.get("上次在途成本")),
                pipeline_cost_current = safe_decimal(row.get("本次在途成本")),

                contract_change_last = safe_decimal(row.get("上次合同+变更(不含在途)")),
                contract_change_current = safe_decimal(row.get("本次合同+变更(不含在途)")),

                estimate_change_last = safe_decimal(row.get("上次预估变更")),
                estimate_change_current = safe_decimal(row.get("本次预估变更")),

                pending_plan_last = safe_decimal(row.get("上次待发生合约规划")),
                pending_plan_current = safe_decimal(row.get("本次待发生合约规划")),

                dynamic_cost_last = safe_decimal(row.get("上次动态成本")),
                dynamic_cost_current = safe_decimal(row.get("本次动态成本")),

                code = row.get("code", ""),
                level = int(row.get("level", 0) or 0),
                full_path = row.get("full_path", ""),
                level_0 = row.get("level_0", None),
                level_1 = row.get("level_1", None),
                level_2 = row.get("level_2", None),
                level_3 = row.get("level_3", None),
            )
            records.append(record)            
            full_path = row.get('full_path', '')
            vn.add_documentation(full_path)

        except Exception as e:
            print(f"❌ 第{i}行解析失败: {e}")
            print("Row content:", row.to_dict())
    print("==========数据已经成功导入至向量数据库============")

    try:
        db.bulk_save_objects(records)
        db.commit()
    except Exception as e:
        print("❌ 插入数据库失败：", e)
        db.rollback()

    print("==========数据已经成功导入至数据库============")




# ================== 处理数据 - 表4成本汇总 =======================
def solve_data_table_4(df):
    print("==========开始数据清洗============")

    # 规范列名与层级识别
    df = df.rename(columns={"科目代码": "code", "成本科目名称": "subject_name"})
    df["code"] = df["code"].astype(str).str.strip()
    df["level"] = df["code"].str.count(r"\.") + 1
    # 如果 subject_name 是“项目成本”，强制 level 为 0
    df.loc[df["subject_name"] == "项目成本", "level"] = 0


    # 仅保留最多三级数据
    df = df[df["level"] <= 3].copy()

    # 初始化层级列与 full_path
    df["level_1"] = ""
    df["level_2"] = ""
    df["level_3"] = ""
    df["full_path"] = ""

    # 构建 code -> subject_name 映射
    code_name_map = dict(zip(df["code"], df["subject_name"]))
    hierarchy_cols = ['level_1', 'level_2', 'level_3']

    for idx, row in df.iterrows():
        code = row["code"]
        level = row["level"]
        parts = code.split(".")
        subject_name = row['subject_name']

        # 1. 填充当前层级（level_n）
        if 0 <= level <= len(hierarchy_cols):
            df.at[idx, hierarchy_cols[level-1]] = subject_name


        path = []

        for i in range(min(len(parts), 3)):  # 最多三级
            parent_code = ".".join(parts[:i+1])
            parent_name = code_name_map.get(parent_code, "")
            if parent_name:
                path.append(parent_name)

        df.at[idx, "full_path"] = " > ".join(path)

    amount_fields = [
    "含税总成本（销售+持有）（万元）",
    "不含税总成本（销售+持有）（万元）",
    "总进税（销售+持有）（万元）",
    "含税建面单方（元/m2）",
    "含税可售单方（元/m2）",
    "占总成本比例"
    ]

    df = clean_amount_fields(df, amount_fields)
    print("==========数据清洗完成============")

    print("==========开始将数据保存至数据库============")
    df = df.replace({np.nan: None})
    records = []
    ddl = str(CreateTable(CostSubjectSummary.__table__).compile(dialect=mysql.dialect()))
    print("==========开始将数据保存至向量数据库============")
    vn.add_ddl(ddl)
    vn.add_ddl_details(ddl)

    for i, row in df.iterrows():
        try:
            record = CostSubjectSummary(
                id=str(uuid.uuid4()),
                flag=row.get("标记", ""),
                subject_code=row.get("code", ""),
                subject_name=row.get("subject_name", ""),

                cost_total_tax=safe_decimal(row.get("含税总成本（销售+持有）（万元）")),
                cost_total_no_tax=safe_decimal(row.get("不含税总成本（销售+持有）（万元）")),
                tax_total=safe_decimal(row.get("总进税（销售+持有）（万元）")),
                unit_cost_building=safe_decimal(row.get("含税建面单方（元/m2）")),
                unit_cost_saleable=safe_decimal(row.get("含税可售单方（元/m2）")),
                cost_ratio=safe_decimal(row.get("占总成本比例")),

                level=int(row.get("level", 0) or 0),
                full_path=row.get("full_path", ""),
                level_1=row.get("level_1", None),
                level_2=row.get("level_2", None),
                level_3=row.get("level_3", None))

            records.append(record)          
            ## "❌ 此处full_path还有问题
            full_path = row.get('full_path', '')
            vn.add_documentation(full_path)

        except Exception as e:
            print(f"❌ 第{i}行解析失败: {e}")
            print("Row content:", row.to_dict())
    print("==========数据已经成功导入至向量数据库============")

    try:
        db.bulk_save_objects(records)
        db.commit()
    except Exception as e:
        print("❌ 插入数据库失败：", e)
        db.rollback()

    print("==========数据已经成功导入至数据库============")




def solve_data_other():
    pass